package com.metacube.questionbank.dao;

import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.metacube.questionbank.dto.QuestionDTO;
import com.metacube.questionbank.dto.TagDTO;
import com.metacube.questionbank.jdbc.QuestionRowMapper;

@Repository
public class QuestionDAO {

	@Autowired
	DataSource dataSource;

	public int insertQuestion(QuestionDTO qDTO) {

		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		String query = "insert into question(TITLE, BODY, USERID, QASKEDDATE, MODIFIEDDATE)"
				+ " values(?,?,?,?,?)";

		System.out.println(qDTO.getqTitle());

		return (jdbcTemplate.update(
				query,
				new Object[] { qDTO.getqTitle(), qDTO.getqBody(),
						qDTO.getUserId(), qDTO.getqAskedDate(),
						qDTO.getModifiedDate() }));
	}

	public List<QuestionDTO> getAnswerList(QuestionDTO qDTO) {

		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		String query = "select * from answer where QID = ?";

		return (jdbcTemplate.query(query, new Object[] { qDTO.getqId() },
				new QuestionRowMapper()));
	}

	public List<QuestionDTO> getQuesId(String title, int userId) {

		List<QuestionDTO> questionList = new ArrayList<QuestionDTO>();

		String query = "select * from question where TITLE = ? and USERID = ?";

		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

		questionList = jdbcTemplate.query(query,
				new Object[] { title, userId }, new QuestionRowMapper());

		return questionList;
	}

	public List<QuestionDTO> getMyQuestionsFromDB(int userId) {

		List<QuestionDTO> myQuestionList = new ArrayList<QuestionDTO>();

		String query = "select q.qId, q.qASkeddate, q.Title, q.Body, q.userId, q.isActive, q.modifiedDate, q.isClosed, q.voteLike, q.voteUnlike, u.name from question q, user u where u.userId = q.userId and q.userId = ?";

		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

		myQuestionList = jdbcTemplate.query(query, new Object[] { userId },
				new QuestionRowMapper());

		return myQuestionList;

	}

	public List<QuestionDTO> getRecentQuestions() {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		String query = "select q.qId, q.qASkeddate, q.Title, q.Body, q.userId, q.isActive, q.modifiedDate, q.isClosed, q.voteLike, q.voteUnlike, u.name from question q, user u where u.userId = q.userId";
		return (jdbcTemplate.query(query, new QuestionRowMapper()));
	}

	public List<QuestionDTO> getUnansweredQuestionsFromDB() {

		List<QuestionDTO> unansweredQuestionList = new ArrayList<QuestionDTO>();

		String query = "select q.qId, q.qASkeddate, q.Title, q.Body, q.userId, q.isActive, q.modifiedDate, q.isClosed, q.voteLike, q.voteUnlike, u.name from question q, user u, answer a where u.userId = q.userId and a.qId != q.qId";

		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

		unansweredQuestionList = jdbcTemplate.query(query,
				new QuestionRowMapper());

		return unansweredQuestionList;
	}

	public List<QuestionDTO> getQuestionAndAnswerFromDB(int qId) {

		List<QuestionDTO> questionList = new ArrayList<QuestionDTO>();

		String query = "select * from question where QID = ?";

		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

		questionList = jdbcTemplate.query(query,
				new Object[] {qId}, new QuestionRowMapper());

		return questionList;
	}
	
	public List<QuestionDTO> getTagQuestions(TagDTO tDTO) {

		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		String query="select q.* from questionTags qt,tag t,questions q where qt.tagID=1 and qt.tagId=t.tagId and q.qId=qt.qId;";
		return (jdbcTemplate.query(query, new QuestionRowMapper()));
	}

}
